*****
Index
*****

CREATE INDEX
============

Creates an index to a specified table by using the **CREATE INDEX** statement. For how to write index name, :doc:`/sql/identifier`.

For how to use indexes on the **SELECT** statement like Using SQL Hint, Descending Index, Covering Index, **ORDER BY** Optimization and **GROUP BY** Optimization, see :doc:`/sql/tuning`.

::

    CREATE [ UNIQUE ] INDEX [index_name]
    ON table_name <index_col_desc>
     
    <index_col_desc> ::=
        ( column_name[(prefix_length)] [ASC | DESC] [ {, column_name[(prefix_length)] [ASC | DESC]} ...] ) 

*   **UNIQUE** : Creates an index with unique values.
*   *index_name* : Specifies the name of the index to be created. The index name must be unique in the table.
*   *table_name* : Specifies the name of the table where the index is to be created.
*   *column_name* : Specifies the name of the column where the index is to be applied. To create a composite index, specify two or more column names.
*   *prefix_length* : When you specify an index for character- or bit string-type column, you can create an index by specifying the beginning part of the column name as a prefix. You can specify the length of the prefix as the number of characters in parentheses next to the column name. You cannot specify *prefix_length* in a multiple column index or a **UNIQUE** index. It is impossible to create an index by specifying *prefix_length* as a host variable. If you want to guarantee the query result order in the index in which *prefix_length* is specified, you must specify the **ORDER BY** clause.
*   **ASC** | **DESC** : Specifies the sorting order of columns. 

.. note:: 

    From the CUBRID 9.0 version, an index name cannot be omitted.

The following example shows how to create a descending index.

.. code-block:: sql

    CREATE INDEX gold_index ON participant(gold DESC);

The following example shows how to create a multiple column index.

.. code-block:: sql

    CREATE INDEX name_nation_idx ON athlete(name, nation_code);

The following example shows how to create a prefix index. In this example, 1-byte long prefix is specified for the *nation_code* column when creating an index.

.. code-block:: sql

    CREATE INDEX idx_game_nation_code ON game(nation_code(1));

ALTER INDEX
===========

The **ALTER INDEX** statement rebuilds an index. In other words, it drops and rebuilds an index. If a table name and column names are added at the end of the **ON** clause, a new index is re-created with the table and column names. ::

    ALTER [UNIQUE] INDEX [index_name]
    [ON table_name [<index_col_desc>]] REBUILD
     
    <index_col_desc> ::=
        ( column_name[(prefix_length)] [ASC | DESC] [ {, column_name[(prefix_length)] [ASC | DESC]} ...] )

*   **UNIQUE** : Specifies that an index to be recreated is an unique index.
*   *index_name* : Specifies the name of the index to be recreated. The index name must be unique in the table.
*   *table_name* : Specifies the name of the table where the index is recreated.
*   *column_name* : Specifies the name of the column where the index is to be applied. To create a composite index, specify two or more column names.
*   *prefix_length* : When you specify an index for character- or bit string-type column, you can create an index by specifying the beginning part of the column name as a prefix. You can specify the length of the prefix as the number of characters in parentheses next to the column name. You cannot specify *prefix_length* in a multiple column index or a **UNIQUE** index. It is impossible to create an index by specifying *prefix_length* as a host variable. If you want to guarantee the query result order in the index in which *prefix_length* is specified, you must specify the **ORDER BY** clause.
*   **ASC** | **DESC** : Specifies the sorting order of columns. 

The following is an example of re-creating indexes in various ways:

.. code-block:: sql

    ALTER INDEX i_game_medal ON game(medal) REBUILD;
    ALTER INDEX game_date_idx REBUILD;

DROP INDEX
==========

Use the **DROP INDEX** statement to drop an index. ::

    DROP [ UNIQUE ] INDEX index_name
    [ON table_name] [ ; ]

*   **UNIQUE** : Specifies that the index to be dropped is a unique index. This also can be dropped with **DROP CONSTRAINT** clause.
*   *index_name* : Specifies the name of the index to be dropped.
*   *table_name* : Specifies the name of the table whose index is dropped.

The following is an example of dropping an index:

.. code-block:: sql

    DROP INDEX game_date_idx ON game;